﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using NTQOnlineExam.Services.Infrastructure;
using NTQOnlineExam.Services.Models;

namespace NTQOnlineExam.Services.DBCommand.Section
{
    public class GetSectionByTermListDbCommand : ICommand<IList<SectionDTO>>
    {
        private IList<int> _terms;
        private const string _Sql = @" 
                                     select s.TermId, s.SectionId, s.SectionName, Count(q.QuestionId) as TotalQuestion
                                         from dbo.Section s 
                                         LEFT JOIN dbo.Question q ON s.SectionId = q.SectionId
                                         WHERE s.TermId IN ({0})
                                         GROUP BY s.TermId, s.SectionId, s.SectionName 
                                 
                                ";
        public GetSectionByTermListDbCommand(IList<int> terms)
        {
            _terms = terms;
        }

        public IList<SectionDTO> Execute()
        {
            if(_terms == null ||_terms.Count == 0)return new List<SectionDTO>();
            var connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
            var dt = new DataTable();
            using (var con = new SqlConnection(connectionString))
            {
                using (var cmd = new SqlCommand(BuildQuery(), con))
                {
                    var adapt = new SqlDataAdapter(cmd);
                    adapt.SelectCommand.CommandType = CommandType.Text;
                    con.Open();
                    adapt.Fill(dt);
                    cmd.ExecuteNonQuery();
                }
                return GetListSectionDTOs(dt);
            }
        }

        private string BuildQuery()
        {
            string ids = string.Join(",", _terms.ToArray());
            return string.Format(_Sql, ids);
        }
        private IList<SectionDTO> GetListSectionDTOs(DataTable dtTable)
        {
            var sectionDTOs = new List<SectionDTO>();
            foreach (DataRow dtRow in dtTable.Rows)
            {
                var sectionDTO = new SectionDTO();
                sectionDTO.SectionId = (int)dtRow["SectionId"];
                sectionDTO.SectionName = dtRow["SectionName"].ToString();
                sectionDTO.TermId = (int)dtRow["TermId"];
                sectionDTO.TotalQuestion = (int)dtRow["TotalQuestion"];
                sectionDTOs.Add(sectionDTO);
            }
            return sectionDTOs;
        }
    }
}
